#!/usr/bin/env python
# -*- coding:utf-8 -*-
from datetime import datetime

from conn.MyLogs import MyLogs
from excel.PruTuutDb import PruTuutDb
from members.MemberPointLevelService import MemberPointLevelService
from members.beans.MemberObj import MemberObj
from members.beans.MemberPointLevelObj import MemberPointLevelObj
from members.beans.MemberWieghtObj import MemberWieghtObj


class MembersService:
    std_array = {}
    conn = None
    pointLevelServiceImpl = None
    pointHistoryImpl = None

    def __init__(self, conn):
        self.conn = conn
        self.pointLevelServiceImpl = MemberPointLevelService(conn)

    def fetch(self, param):
        try:
            count_sql = f" SELECT count(`MEMBER_ID`) FROM `view_member` as mem_member WHERE 1=1 %s"
            fetch_sql = f" SELECT {MemberObj.to_string(True)}" \
                        f" FROM `view_member` as mem_member WHERE 1=1 %s "
            where_sql = ""
            if len(param['key']) > 0:
                where_sql = where_sql + f" AND CONCAT(`MEMBER_ID`,`NAME`, `UNAME`) like CONCAT('%',\'{param['key']}\','%') "
            where_sql += " ORDER BY `STATUS` ASC,`REGISTER_TIME` DESC "
            int_begin = 0
            if int(param['pageNum']) > 1:
                int_begin = (int(param['pageNum']) - 1) * int(param['pageSize'])
            elif int(param['pageNum']) == 1:
                int_begin = 0 * int(param['pageSize'])

            where_sql_no_limit = where_sql
            where_sql_limit = where_sql_no_limit + f" LIMIT {int_begin}, {int(param['pageSize'])}"
            data_total = self.conn.sql_fetch(count_sql % where_sql_no_limit)
            data_list = self.conn.sql_fetch(fetch_sql % where_sql_limit)
            items = []
            if len(data_list) > 0:
                items = [MemberObj.load_db_2_dict(data=item) for item in data_list]
            return {"code": 200, "data": items, "total": data_total[0][0]}
        except Exception as e:
            MyLogs.logger.error("ERROR: %s" % e)
        return {"code": 500, "data": None}

    def fetch_one(self, member_id):
        fetch_sql = f"SELECT {MemberObj.to_string(True)} FROM `view_member` as mem_member WHERE 1 = 1 " \
                    f" AND `MEMBER_ID` = \'{member_id}\'"
        try:
            items = self.conn.sql_fetch(fetch_sql)
            if len(items) > 0:
                res = [MemberObj.load_db_2_dict(item) for item in items]
                return {"code": 200, "data": res}
        except Exception as e:
            print(e)
        return {"code": 500, "data": None}

    # 加入了事务控制，优先注册会员，然后修改会员等级
    def add_member(self, member: object):
        try:
            pruTuut_conn = PruTuutDb.get_conn()
            # 开始配置事务
            pruTuut_conn.begin()
            cursor = pruTuut_conn.cursor()
            member_id = member['member_id']
            _str_max_member_sno = self.cal_max_member_sno()
            member['MEMBER_SNO'] = int(_str_max_member_sno) + 1
            add_check = self.count_one(member_id=member_id)
            if add_check:
                return {"code": 203, "data": None}
            # print(json.dumps(member, sort_keys=True, ensure_ascii=False, indent=4))
            insert_sql = f" INSERT INTO `mem_member` ({MemberObj.to_string(False)}) " \
                         " VALUES ( %s )" % MemberObj.concat_datas(member)
            cursor.execute(insert_sql)
            # 计算积分和会员等级
            acct_point = float(member["ACCT_POINT"])
            level = self.pointLevelServiceImpl.get_level_with_points(acct_point)
            _mem_level_key = level["key"]
            _mem_level = level["level"]
            update_str = f"`ACCT_POINT`= {acct_point},`level`= '%s', `level_name`= '%s'" % (_mem_level_key, _mem_level)
            update_point_sql = f" UPDATE `mem_member` SET %s WHERE `MEMBER_ID`= \'%s\'" % (update_str, member_id)
            cursor.execute(update_point_sql)
            pruTuut_conn.commit()
            return {"code": 200, "data": None}
        except Exception as e:
            pruTuut_conn.rollback()
            print(e)
        finally:
            pruTuut_conn.close()
        return {"code": 500, "data": None}

    def update_member(self, member, member_id):
        # 获取当前时间
        current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        try:
            pruTuut_conn = PruTuutDb.get_conn()
            # 开始配置事务
            pruTuut_conn.begin()
            cursor = pruTuut_conn.cursor()
            add_check = self.count_one(member_id=member['member_id'])
            if not add_check:
                return {"code": 404, "data": None}
            old_member = self.fetch_one(member_id=member['member_id'])
            update_str = MemberObj.split_params(member)
            update_sql = f" UPDATE `mem_member` SET %s, `UPDATE_DATE`=\'{current_time}\'" \
                         f" WHERE `MEMBER_ID`= \'%s\'" % (update_str, member_id)
            cursor.execute(update_sql)
            # 计算积分和会员等级
            acct_point = float(member['grow_value']) + old_member['data'][0]['acct_point']
            # 有效积分 = 总积分 - 上一次的使用积分值
            effect_point = acct_point - old_member['data'][0]['used_point']
            level = self.pointLevelServiceImpl.get_level_with_points(acct_point)
            if level['code'] == 200:
                _mem_level_key = level['data']["key"]
                _mem_level = level['data']["level"]
                update_str = f"`ACCT_POINT`= {acct_point}, `EFFECT_POINT`= {effect_point}," \
                             f" `level`= '%s', `level_name`= '%s'" % (_mem_level_key, _mem_level)
                update_point_sql = f" UPDATE `mem_member` SET %s WHERE `MEMBER_ID`= \'%s\'" % (update_str, member_id)
                cursor.execute(update_point_sql)
                # 计算的等级与用户原等级不相同，则更新升级或降级时间
                if not _mem_level_key == old_member['data'][0]['level']:
                    update_str = f"`UPGRADE_TIME`= \'{current_time}\'"
                    update_level_sql = f" UPDATE `mem_member` SET %s WHERE `MEMBER_ID`= \'%s\'" % (
                        update_str, member_id)
                    cursor.execute(update_level_sql)
            else:
                level = self.pointLevelServiceImpl.get_level_with_keys(MemberPointLevelObj.MEMBER_DEFAULT_LEVEL)
                update_str = f"`ACCT_POINT`= {acct_point}, `EFFECT_POINT`= {effect_point}," \
                             f" `level`= '%s', `level_name`= '%s'" % (
                                 MemberPointLevelObj.MEMBER_DEFAULT_LEVEL, level['data']['level'])
                update_level_sql = f" UPDATE `mem_member` SET %s WHERE `MEMBER_ID`= \'%s\'" % (update_str, member_id)
                self.conn.execute(update_level_sql)
            pruTuut_conn.commit()
            return {"code": 200, "data": None}
        except Exception as e:
            pruTuut_conn.rollback()
            print(e)
        finally:
            pruTuut_conn.close()
        return {"code": 500, "data": None}

    def drop_member(self, member_id):
        # 获取当前时间
        current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        add_check = self.count_one(member_id=member_id)
        if not add_check:
            return {"code": 404, "data": None}
        old_data = self.fetch_one(member_id=member_id)
        # execute_SQL = f" UPDATE `mem_member` SET " \
        #               f" `STATUS` = \'{MemberObj.MEM_STATUS['DROP']}\'," \
        #               f" `UPDATE_DATE`=\'{current_time}\'" \
        #               f" WHERE `MEMBER_ID` = \'{member_id}\'"
        try:
            pruTuut_conn = PruTuutDb.get_conn()
            # 开始配置事务
            pruTuut_conn.begin()
            cursor = pruTuut_conn.cursor()
            execute_SQL_A = f" DELETE FROM `mem_member_weight` WHERE `member_id` = \'{old_data['data'][0]['member_id']}\' "
            cursor.execute(execute_SQL_A)
            execute_SQL_B = f" DELETE FROM `mem_member` WHERE `member_id` = \'{old_data['data'][0]['member_id']}\' "
            cursor.execute(execute_SQL_B)
            pruTuut_conn.commit()
        except Exception as e:
            pruTuut_conn.rollback()
            print(e)
        finally:
            pruTuut_conn.close()
        return {"code": 200, "data": None}

    def res_member(self, member_id):
        # 获取当前时间
        current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        add_check = self.count_one(member_id=member_id)
        if not add_check:
            return {"code": 404, "data": None}
        execute_SQL = f" UPDATE `mem_member` SET " \
                      f" `STATUS` = \'{MemberObj.MEM_STATUS['INUSE']}\'," \
                      f" `UPDATE_DATE`=\'{current_time}\'" \
                      f" WHERE `MEMBER_ID` = \'{member_id}\'"
        self.conn.sql_commit(execute_SQL)
        return {"code": 200, "data": None}

    def act_member(self, member_id):
        # 获取当前时间
        current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        add_check = self.count_one(member_id=member_id)
        if not add_check:
            return {"code": 404, "data": None}
        execute_SQL = f" UPDATE `mem_member` SET " \
                      f" `STATUS` = \'{MemberObj.MEM_STATUS['INUSE']}\'," \
                      f" `UPDATE_DATE`=\'{current_time}\'" \
                      f" WHERE `MEMBER_ID` = \'{member_id}\'"
        self.conn.sql_commit(execute_SQL)
        return {"code": 200, "data": None}

    def stop_member(self, member_id):
        # 获取当前时间
        current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        add_check = self.count_one(member_id=member_id)
        if not add_check:
            return {"code": 404, "data": None}
        execute_SQL = f" UPDATE `mem_member` SET " \
                      f" `STATUS` = \'{MemberObj.MEM_STATUS['STOP']}\'," \
                      f" `UPDATE_DATE`=\'{current_time}\'" \
                      f" WHERE `MEMBER_ID` = \'{member_id}\'"
        self.conn.sql_commit(execute_SQL)
        return {"code": 200, "data": None}

    def count_one(self, member_id) -> bool:
        fetch_sql = f" SELECT count(`MEMBER_ID`) FROM `mem_member` WHERE 1 = 1 " \
                    f" AND `MEMBER_ID` = \'{member_id}\'"
        try:
            items = self.conn.sql_fetch(fetch_sql)
            if len(items) > 0:
                return items[0][0] > 0
            return False
        except Exception as e:
            print(e)
        return {"code": 500, "data": None}

    def cal_max_member_sno(self):
        fetch_sql = f" SELECT max(`MEMBER_SNO`) as `MAX_MEMBER_SNO` FROM  `mem_member` "
        try:
            items = self.conn.sql_fetch(fetch_sql)
            if len(items) > 0:
                return items[0][0]
            return 0
        except Exception as e:
            print(e)
        return {"code": 500, "data": None}

    def reg_member(self, member):
        import hashlib
        same_add = False
        try:
            pruTuut_conn = PruTuutDb.get_conn()
            # 验证uname是否重复注册
            fetch_sql = f"SELECT count(`MEMBER_ID`) FROM `mem_member` WHERE 1 = 1 " \
                        f" AND `UNAME` = \'{member['uname']}\'"
            items = self.conn.sql_fetch(fetch_sql)
            if len(items) > 0:
                if items[0][0] > 0:
                    same_add = True
            if same_add:
                return {"code": 501, "data": None}
            # 获取当前时间
            current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            str_member_id = MemberObj.reg_memeber_id()
            member['member_id'] = str_member_id
            # _str_max_member_sno = self.cal_max_member_sno()
            member['member_sno'] = None
            member['member_code'] = member['uname']
            member['mobile'] = member['uname']
            data = member['uname']
            str_md5_pwd = hashlib.md5(data.encode(encoding='UTF-8')).hexdigest()
            member['password'] = str_md5_pwd
            member['login_count'] = float(0)
            # member['register_type'] = 1 # HCM会员 1
            member['new_user'] = 1
            member['is_agent'] = 0
            member['status'] = MemberObj.MEM_STATUS['STOP']  # 待激活状态
            member['member_type'] = "MEM-001"  # 主数据编码：MEM-001
            member['level'] = MemberPointLevelObj.MEMBER_DEFAULT_LEVEL
            member['level_name'] = MemberPointLevelObj.MEM_LEVEL[MemberPointLevelObj.MEMBER_DEFAULT_LEVEL]
            member['account_type'] = 1100  # 未签约/签约取消 1100
            member['grow_value'] = float(0)
            member['acct_point'] = float(0)
            member['used_point'] = float(0)
            member['effect_point'] = float(0)
            member['invalid_point'] = float(0)
            member['create_date'] = current_time
            member['register_time'] = current_time
            MemberObj.make_reg_data(member)
            # 开始配置事务
            pruTuut_conn.begin()
            cursor = pruTuut_conn.cursor()
            # print(json.dumps(member, sort_keys=True, ensure_ascii=False, indent=4))
            insert_sql = f" INSERT INTO `mem_member` ({MemberObj.to_string(False)}) " \
                         " VALUES ( %s )" % MemberObj.concat_datas(member)
            cursor.execute(insert_sql)
            if member['register_type'] == "1":
                # TODO 2024-03-16 会员注册时候加入体质计算数据
                for key, val in member.items():
                    if val is None:
                        member[key] = None
                    else:
                       if isinstance(val, str):
                           member[key] = val.replace(" ", "")
                       else:
                           member[key] = val
                check_keys = [j["key"] for j in [{"key": key, "val": val} for key, val in member.items()]]
                if "bmi" not in check_keys:
                    cal_bmi = round(float(member["weight"]) / (float(member["height"]) / 100 * float(member["height"]) / 100),2)
                    member["bmi"] = cal_bmi
                # ------------------------------------------------------------------------------------------------------
                member['heatgap'] = MemberWieghtObj.cal_member_heat_gap(member)
                # ------------------------------------------------------------------------------------------------------
                # 成年女性体脂率一般要求在20%-25%之间，男性患者要求在15%-18%左右
                if int(member['sex']) == 0:
                    # 成年男性体脂率的计算公式：参数A=腰围（cm）×0.74，参数B=体重（kg）×0.082+44.74，体脂肪重量（kg）=A-B，体脂率=身体脂肪总重量÷体重×100%。
                    # 男性系数
                    staticCoefficient = 20
                    # 体脂率 = 1.2*BMI+0.23*年龄-16.2
                    FAT = 1.2 * member["bmi"] + 0.23 * int(member['age']) - 16.2
                else:
                    # 成年女性体脂率的计算公式：参数A=腰围（cm）×0.74，参数B=体重（kg）×0.082+34.89。体脂肪重量（kg）=A-B，体脂率=身体脂肪总重量÷体重×100%。
                    # 女性系数
                    staticCoefficient = 18
                    # 体脂率 = 1.2*BMI+0.23*年龄-5.4
                    FAT = 1.2 * member["bmi"] + 0.23 * int(member['age']) - 5.4
                member['bodyFatRat'] = round(FAT, 2)
                add_check = self.count_one(member_id=member['member_id'])
                if not add_check:
                    insert_sql = f" INSERT INTO `mem_member_weight` ({MemberWieghtObj.to_string(False)}) " \
                                 " VALUES ( %s )" % MemberWieghtObj.concat_datas(member)
                    cursor.execute(insert_sql)
                # 提交事务
                pruTuut_conn.commit()
            return {"code": 200, "data": None}
        except Exception as e:
            pruTuut_conn.rollback()
            print(e)
        finally:
            pruTuut_conn.close()
        return {"code": 500, "data": None}

    def update_member_point(self, member: object):
        # 获取当前时间
        current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        member_id = member['member_id']
        try:
            pruTuut_conn = PruTuutDb.get_conn()
            # 开始配置事务
            pruTuut_conn.begin()
            cursor = pruTuut_conn.cursor()
            old_member = self.fetch_one(member_id=member['member_id'])
            # 计算积分和会员等级
            acct_point = float(member['grow_value']) + old_member['data'][0]['acct_point']
            # 有效积分 = 总积分 - 上一次的使用积分值
            effect_point = acct_point - old_member['data'][0]['used_point']
            level = self.pointLevelServiceImpl.get_level_with_points(acct_point)
            if level['code'] == 200:
                _mem_level_key = level['data']["key"]
                _mem_level = level['data']["level"]
                update_str = f"`ACCT_POINT`= {acct_point}, `EFFECT_POINT`= {effect_point}," \
                             f" `level`= '%s', `level_name`= '%s'" % (_mem_level_key, _mem_level)
                update_point_sql = f" UPDATE `mem_member` SET %s WHERE `MEMBER_ID`= \'%s\'" % (update_str, member_id)
                cursor.execute(update_point_sql)
                # 计算的等级与用户原等级不相同，则更新升级或降级时间
                if not _mem_level_key == old_member['data'][0]['level']:
                    update_str = f"`UPGRADE_TIME`= \'{current_time}\'"
                    update_level_sql = f" UPDATE `mem_member` SET %s WHERE `MEMBER_ID`= \'%s\'" % (
                        update_str, member_id)
                    cursor.execute(update_level_sql)
            else:
                level = self.pointLevelServiceImpl.get_level_with_keys(MemberPointLevelObj.MEMBER_DEFAULT_LEVEL)
                update_str = f"`ACCT_POINT`= {acct_point}, `EFFECT_POINT`= {effect_point}," \
                             f" `level`= '%s', `level_name`= '%s'" % (
                                 MemberPointLevelObj.MEMBER_DEFAULT_LEVEL, level['data']['level'])
                update_level_sql = f" UPDATE `mem_member` SET %s WHERE `MEMBER_ID`= \'%s\'" % (update_str, member_id)
                self.conn.sql_commit(update_level_sql)
            pruTuut_conn.commit()
        except Exception as e:
            pruTuut_conn.rollback()
            print(e)
        finally:
            pruTuut_conn.close()

    def update_member_sno(self, member_id, peroject_sno):
        member = self.fetch_one(member_id=member_id)["data"][0]
        member["member_sno"] = peroject_sno
        return self.update_member(member=member, member_id=member_id)
